﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace DAO
{
    public class DataHelperSQLServer
    {
        //protected static String connectionString = "Server=.; Database=HocSinh; Integrated Security=SSPI;";
        protected static String connectionString = "Server=.; Database=giaphav2; Integrated Security=SSPI;";
        public static SqlConnection conn = new SqlConnection(connectionString);
        public static SqlCommand cmd;

        //Tra ve Datatable
        public DataTable ExecQuery(string sql)
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                    SqlDataAdapter bothichung = new SqlDataAdapter(sql, connectionString);
                    DataTable bang = new DataTable();
                    bothichung.Fill(bang);
                    return bang;
                }
                else
                {
                    SqlDataAdapter bothichung = new SqlDataAdapter(sql, connectionString);
                    DataTable bang = new DataTable();
                    bothichung.Fill(bang);
                    return bang;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //mo ket noi den sqlserver
        public void OpenConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

        }

        //Dong ket noi sqlserver
        public void CloseConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        //Thuc hien them, xoa, sua
        public void ExecNonQuery(string sql)
        {
            //Mo ket noi
            CloseConnection();
            SqlCommand command = new SqlCommand(sql, conn);
            command.Connection.Open();
            command.ExecuteNonQuery();
            //Dong ket noi
            CloseConnection();
        }


        public int ExecuteStoredProcedure(string storeProcedureName, string[] parameterNames, object[] parameterValues)
        {
            if (conn.State == ConnectionState.Closed)
            {
                OpenConnection();
                // Khai báo và khởi tạo đối tượng Command với tham số tên thủ tục spName
                cmd = new SqlCommand(storeProcedureName, conn);

                // Khai báo kiểu thủ tục
                cmd.CommandType = CommandType.StoredProcedure;

                // Khai báo tham số SqlParameter
                SqlParameter p;

                // Khởi tạo danh sách các tham số với giá trị tương ứng
                for (int i = 0; i < parameterNames.Length; i++)
                {
                    p = new SqlParameter(parameterNames[i], parameterValues[i]);
                    cmd.Parameters.Add(p);
                }

                return cmd.ExecuteNonQuery();
            }
            else
            {
                // Khai báo và khởi tạo đối tượng Command với tham số tên thủ tục spName
                cmd = new SqlCommand(storeProcedureName, conn);

                // Khai báo kiểu thủ tục
                cmd.CommandType = CommandType.StoredProcedure;

                // Khai báo tham số SqlParameter
                SqlParameter p;

                // Khởi tạo danh sách các tham số với giá trị tương ứng
                for (int i = 0; i < parameterNames.Length; i++)
                {
                    p = new SqlParameter(parameterNames[i], parameterValues[i]);
                    cmd.Parameters.Add(p);
                }

                return cmd.ExecuteNonQuery();
            }
        }
        protected ArrayList ConvertDataTableToArrayList(DataTable dt)
        {
            ArrayList arr = new ArrayList();            
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object obj = GetDataFromDataRow(dt, i);
                arr.Add(obj);

            }
            return arr;
        }
        protected virtual object GetDataFromDataRow(DataTable dt, int i)
        {
            return null;
        }
        

        /*
        //Sử dụng pattern abstract factory
        static DbConnection connection = null;

        static DbProviderFactory factory = null;

        static void ChooseFactory(string providerName)
        {
            factory = DbProviderFactories.GetFactory(providerName);
        }

        public static void OpenConnection()
        {
            if (connection == null)
            {
                connection = factory.CreateConnection();
                connection.ConnectionString = _connectionString;
                //connection = new OleDbConnection(_connectionString);
                connection.Open();
            }
        }

        public static void CloseConnection()
        {
            if (connection != null)
            {
                connection.Close();
            }
        }

        public static DataTable ExecQuery(string sql)
        {
            OpenConnection();
            DataTable dt = new DataTable();
            DbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
            CloseConnection();
            return dt;
        }


        public static void ExecNonQuery(string sql)
        {
            OpenConnection();
            DbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            command.ExecuteNonQuery();
            CloseConnection();
        }

        public static object ExecScalar(string sql)
        {
            OpenConnection();
            DbCommand command = connection.CreateCommand();
            command.CommandText = sql;
            object o = command.ExecuteScalar();
            CloseConnection();
            return o;
        }*/


    }
}